--实际揽收
insert overwrite table jms_dm.dm_business_reach_summary_dt partition (dt, dtype)
select *
from (select null                                                                              taking_site_code,
             null                                                                              sign_site_code,
             nvl(statistics_detail.subscribe_source_code, net_detail.island_code)              subscribe_source_code,
             null                                                                              customer_code,
             null                                                                              customer_name,
             nvl(statistics_detail.subscribe_source_name, net_detail.island_name)              subscribe_source_name,
             nvl(statistics_detail.taking_provider_id, net_detail.start_provider_id)           taking_provider_id,
             nvl(statistics_detail.taking_provider_desc, net_detail.start_provider_desc)       taking_provider_desc,
             nvl(statistics_detail.taking_city_id, net_detail.start_city_id)                   taking_city_id,
             nvl(statistics_detail.taking_city_desc, net_detail.start_city_desc)               taking_city_desc,
             nvl(statistics_detail.taking_agent_code, net_detail.start_subordinate_agent_code) taking_agent_code,
             nvl(statistics_detail.taking_agent_name, net_detail.start_subordinate_agent_name) taking_agent_name,
             null                                                                              taking_site_name,
             nvl(statistics_detail.sign_provider_id, net_detail.sign_provider_id)              sign_provider_id,
             nvl(statistics_detail.sign_provider_desc, net_detail.sign_provider_desc)          sign_provider_desc,
             nvl(statistics_detail.sign_city_id, net_detail.sign_city_id)                      sign_city_id,
             nvl(statistics_detail.sign_city_desc, net_detail.sign_city_desc)                  sign_city_desc,
             nvl(statistics_detail.sign_agent_code, net_detail.sign_subordinate_agent_code)    sign_agent_code,
             nvl(statistics_detail.sign_agent_name, net_detail.sign_subordinate_agent_name)    sign_agent_name,
             null                                                                              sign_site_name,
             statistics_detail.buss_all_cnt,
             statistics_detail.buss_reach_cnt,
             statistics_detail.pres_reach_cnt,
             statistics_detail.effect_cnt,
             statistics_detail.taking_diff_cnt,
             statistics_detail.taking_one_diff_cnt,
             statistics_detail.taking_two_diff_cnt,
             statistics_detail.taking_three_diff_cnt,
             statistics_detail.taking_four_diff_cnt,
             statistics_detail.taking_five_diff_cnt,
             statistics_detail.taking_six_diff_cnt,
             statistics_detail.taking_seven_diff_cnt,
             net_detail.pres_total,
             net_detail.pres_reach_sum,
             if(statistics_detail.taking_city_id is not null, 1, 0)                            flag,
             1                                                                                 statistic_type,
             nvl(statistics_detail.statistic_date, date(net_detail.dt))                        statistic_date,
             nvl(statistics_detail.dtype, 1)                                                   detail_type,
             nvl(statistics_detail.dt, net_detail.dt)                                          dt,
             nvl(statistics_detail.dtype, 1)                                                   dtype
      from (select subscribe_source_code,
                   subscribe_source_name,
                   taking_provider_id,
                   taking_provider_desc,
                   taking_city_id,
                   taking_city_desc,
                   taking_agent_code,
                   taking_agent_name,
                   sign_provider_id,
                   sign_provider_desc,
                   sign_city_id,
                   sign_city_desc,
                   sign_agent_code,
                   sign_agent_name,
                   count(1)                            buss_all_cnt,
                   sum(if(is_buss_reach = 1, 1, 0))    buss_reach_cnt,
                   sum(if(is_pres_reach = 1, 1, 0))    pres_reach_cnt,
                   sum(if(is_effect = 1, 1, 0))        effect_cnt,
                   sum(if(taking_time_diff > 0, 1, 0)) taking_diff_cnt,
                   sum(is_taking_one_diff)             taking_one_diff_cnt,
                   sum(is_taking_two_diff)             taking_two_diff_cnt,
                   sum(is_taking_three_diff)           taking_three_diff_cnt,
                   sum(is_taking_four_diff)            taking_four_diff_cnt,
                   sum(is_taking_five_diff)            taking_five_diff_cnt,
                   sum(is_taking_six_diff)             taking_six_diff_cnt,
                   sum(is_taking_seven_diff)           taking_seven_diff_cnt,
                   date(dt)                            statistic_date,
                   dt,
                   dtype
            from jms_dm.dm_business_reach_details_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and dtype = 1
            group by dt,
                     dtype,
                     taking_provider_id,
                     taking_provider_desc,
                     taking_city_id,
                     taking_city_desc,
                     taking_agent_code,
                     taking_agent_name,
                     sign_provider_id,
                     sign_provider_desc,
                     sign_city_id,
                     sign_city_desc,
                     sign_agent_code,
                     sign_agent_name,
                     subscribe_source_code,
                     subscribe_source_name) statistics_detail
               full join (select start_city_id,
                                 start_city_desc,
                                 start_subordinate_agent_code,
                                 start_subordinate_agent_name,
                                 start_provider_id,
                                 start_provider_desc,
                                 sign_subordinate_agent_code,
                                 sign_subordinate_agent_name,
                                 sign_provider_id,
                                 sign_provider_desc,
                                 sign_city_id,
                                 sign_city_desc,
                                 island_code,
                                 island_name,
                                 sum(cainiao_pres_platform_total)     pres_total,
                                 sum(cainiao_pres_platform_reach_sum) pres_reach_sum,
                                 dt
                          from jms_dm.dm_city_pre_reach_rate_dt
                          where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                              and '{{ execution_date | cst_ds }}'
                            and cainiao_platform_name is not null
                            and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
                            and (is_pres_sign = '1'
                              or is_pres_sign = '0')
                            and date_time is not null
                          group by start_city_id,
                                   start_city_desc,
                                   start_subordinate_agent_code,
                                   start_subordinate_agent_name,
                                   start_provider_id,
                                   start_provider_desc,
                                   sign_subordinate_agent_code,
                                   sign_subordinate_agent_name,
                                   sign_provider_id,
                                   sign_provider_desc,
                                   sign_city_id,
                                   sign_city_desc,
                                   island_code,
                                   island_name,
                                   dt) net_detail on statistics_detail.taking_city_id = net_detail.start_city_id
          and statistics_detail.sign_city_id = net_detail.sign_city_id
          and statistics_detail.taking_agent_code = net_detail.start_subordinate_agent_code
          and statistics_detail.sign_agent_code = net_detail.sign_subordinate_agent_code
          and statistics_detail.taking_provider_id = net_detail.start_provider_id
          and statistics_detail.sign_provider_id = net_detail.sign_provider_id
          and statistics_detail.subscribe_source_code = net_detail.island_code
          and net_detail.dt = statistics_detail.dt
      union all
      select nvl(statistics_detail.taking_site_code, net_detail.start_pick_network_code)       taking_site_code,
             null                                                                              sign_site_code,
             nvl(statistics_detail.subscribe_source_code, net_detail.island_code)              subscribe_source_code,
             nvl(statistics_detail.customer_code, net_detail.customer_code)                    customer_code,
             nvl(statistics_detail.customer_name, net_detail.customer_name)                    customer_name,
             nvl(statistics_detail.subscribe_source_name, net_detail.island_name)              subscribe_source_name,
             nvl(statistics_detail.taking_provider_id, net_detail.start_provider_id)           taking_provider_id,
             nvl(statistics_detail.taking_provider_desc, net_detail.start_provider_desc)       taking_provider_desc,
             nvl(statistics_detail.taking_city_id, net_detail.start_city_id)                   taking_city_id,
             nvl(statistics_detail.taking_city_desc, net_detail.start_city_desc)               taking_city_desc,
             nvl(statistics_detail.taking_agent_code, net_detail.start_subordinate_agent_code) taking_agent_code,
             nvl(statistics_detail.taking_agent_name, net_detail.start_subordinate_agent_name) taking_agent_name,
             nvl(statistics_detail.taking_site_name, net_detail.start_pick_network_name)       taking_site_name,
             null                                                                              sign_provider_id,
             null                                                                              sign_provider_desc,
             null                                                                              sign_city_id,
             null                                                                              sign_city_desc,
             null                                                                              sign_agent_code,
             null                                                                              sign_agent_name,
             null                                                                              sign_site_name,
             statistics_detail.buss_all_cnt,
             statistics_detail.buss_reach_cnt,
             statistics_detail.pres_reach_cnt,
             statistics_detail.effect_cnt,
             statistics_detail.taking_diff_cnt,
             statistics_detail.taking_one_diff_cnt,
             statistics_detail.taking_two_diff_cnt,
             statistics_detail.taking_three_diff_cnt,
             statistics_detail.taking_four_diff_cnt,
             statistics_detail.taking_five_diff_cnt,
             statistics_detail.taking_six_diff_cnt,
             statistics_detail.taking_seven_diff_cnt,
             net_detail.pres_total,
             net_detail.pres_reach_sum,
             if(statistics_detail.taking_site_code is not null, 1, 0)                          flag,
             2                                                                                 statistic_type,
             nvl(statistics_detail.statistic_date, date(net_detail.dt))                        statistic_date,
             nvl(statistics_detail.dtype, 1)                                                   detail_type,
             nvl(statistics_detail.dt, net_detail.dt)                                          dt,
             nvl(statistics_detail.dtype, 1)                                                   dtype
      from (select taking_site_code,
                   subscribe_source_code,
                   customer_code,
                   customer_name,
                   subscribe_source_name,
                   taking_provider_id,
                   taking_provider_desc,
                   taking_city_id,
                   taking_city_desc,
                   taking_agent_code,
                   taking_agent_name,
                   taking_site_name,
                   count(1)                            buss_all_cnt,
                   sum(if(is_buss_reach = 1, 1, 0))    buss_reach_cnt,
                   sum(if(is_pres_reach = 1, 1, 0))    pres_reach_cnt,
                   sum(if(is_effect = 1, 1, 0))        effect_cnt,
                   sum(if(taking_time_diff > 0, 1, 0)) taking_diff_cnt,
                   sum(is_taking_one_diff)             taking_one_diff_cnt,
                   sum(is_taking_two_diff)             taking_two_diff_cnt,
                   sum(is_taking_three_diff)           taking_three_diff_cnt,
                   sum(is_taking_four_diff)            taking_four_diff_cnt,
                   sum(is_taking_five_diff)            taking_five_diff_cnt,
                   sum(is_taking_six_diff)             taking_six_diff_cnt,
                   sum(is_taking_seven_diff)           taking_seven_diff_cnt,
                   date(dt)                            statistic_date,
                   dt,
                   dtype
            from jms_dm.dm_business_reach_details_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and dtype = 1
            group by dt,
                     dtype,
                     taking_provider_id,
                     taking_provider_desc,
                     taking_city_id,
                     taking_city_desc,
                     taking_agent_code,
                     taking_agent_name,
                     taking_site_name,
                     taking_site_code,
                     subscribe_source_code,
                     customer_code,
                     customer_name,
                     subscribe_source_name) statistics_detail
               full join (select start_pick_network_code,
                                 start_pick_network_name,
                                 start_city_id,
                                 start_city_desc,
                                 start_provider_id,
                                 start_provider_desc,
                                 start_subordinate_agent_code,
                                 start_subordinate_agent_name,
                                 island_code,
                                 island_name,
                                 customer_code,
                                 customer_name,
                                 sum(cainiao_pres_platform_total)     pres_total,
                                 sum(cainiao_pres_platform_reach_sum) pres_reach_sum,
                                 dt
                          from jms_dm.dm_net_pre_reach_rate_dt
                          where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                              and '{{ execution_date | cst_ds }}'
                            and cainiao_platform_name is not null
                            and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
                            and (is_pres_sign = '1'
                              or is_pres_sign = '0')
                            and date_time is not null
                          group by start_pick_network_code,
                                   start_pick_network_name,
                                   start_city_id,
                                   start_city_desc,
                                   start_provider_id,
                                   start_provider_desc,
                                   start_subordinate_agent_code,
                                   start_subordinate_agent_name,
                                   island_code,
                                   island_name,
                                   customer_code,
                                   customer_name,
                                   dt) net_detail
                         on      statistics_detail.taking_site_code = net_detail.start_pick_network_code
                             and statistics_detail.taking_city_id = net_detail.start_city_id
                             and statistics_detail.taking_provider_id = net_detail.start_provider_id
                             and statistics_detail.taking_agent_code = net_detail.start_subordinate_agent_code
                             and statistics_detail.subscribe_source_code = net_detail.island_code
                             and statistics_detail.subscribe_source_name = net_detail.island_name
                             and statistics_detail.customer_code = net_detail.customer_code
                             and statistics_detail.customer_name = net_detail.customer_name
                             and net_detail.dt = statistics_detail.dt) detail distribute by dt, dtype, PMOD(HASH(RAND()), 3);


--实际揽收
insert overwrite table jms_dm.dm_business_reach_summary_dt partition (dt, dtype)
select *
from (select null                                                                              taking_site_code,
             null                                                                              sign_site_code,
             nvl(statistics_detail.subscribe_source_code, net_detail.island_code)              subscribe_source_code,
             null                                                                              customer_code,
             null                                                                              customer_name,
             nvl(statistics_detail.subscribe_source_name, net_detail.island_name)              subscribe_source_name,
             nvl(statistics_detail.taking_provider_id, net_detail.start_provider_id)           taking_provider_id,
             nvl(statistics_detail.taking_provider_desc, net_detail.start_provider_desc)       taking_provider_desc,
             nvl(statistics_detail.taking_city_id, net_detail.start_city_id)                   taking_city_id,
             nvl(statistics_detail.taking_city_desc, net_detail.start_city_desc)               taking_city_desc,
             nvl(statistics_detail.taking_agent_code, net_detail.start_subordinate_agent_code) taking_agent_code,
             nvl(statistics_detail.taking_agent_name, net_detail.start_subordinate_agent_name) taking_agent_name,
             null                                                                              taking_site_name,
             nvl(statistics_detail.sign_provider_id, net_detail.sign_provider_id)              sign_provider_id,
             nvl(statistics_detail.sign_provider_desc, net_detail.sign_provider_desc)          sign_provider_desc,
             nvl(statistics_detail.sign_city_id, net_detail.sign_city_id)                      sign_city_id,
             nvl(statistics_detail.sign_city_desc, net_detail.sign_city_desc)                  sign_city_desc,
             nvl(statistics_detail.sign_agent_code, net_detail.sign_subordinate_agent_code)    sign_agent_code,
             nvl(statistics_detail.sign_agent_name, net_detail.sign_subordinate_agent_name)    sign_agent_name,
             null                                                                              sign_site_name,
             statistics_detail.buss_all_cnt,
             statistics_detail.buss_reach_cnt,
             statistics_detail.pres_reach_cnt,
             statistics_detail.effect_cnt,
             statistics_detail.taking_diff_cnt,
             statistics_detail.taking_one_diff_cnt,
             statistics_detail.taking_two_diff_cnt,
             statistics_detail.taking_three_diff_cnt,
             statistics_detail.taking_four_diff_cnt,
             statistics_detail.taking_five_diff_cnt,
             statistics_detail.taking_six_diff_cnt,
             statistics_detail.taking_seven_diff_cnt,
             net_detail.pres_total,
             net_detail.pres_reach_sum,
             if(statistics_detail.taking_city_id is not null, 1, 0)                            flag,
             1                                                                                 statistic_type,
             nvl(statistics_detail.statistic_date, date(net_detail.dt))                        statistic_date,
             nvl(statistics_detail.dtype, 2)                                                   detail_type,
             nvl(statistics_detail.dt, net_detail.dt)                                          dt,
             nvl(statistics_detail.dtype, 2)                                                   dtype
      from (select subscribe_source_code,
                   subscribe_source_name,
                   taking_provider_id,
                   taking_provider_desc,
                   taking_city_id,
                   taking_city_desc,
                   taking_agent_code,
                   taking_agent_name,
                   sign_provider_id,
                   sign_provider_desc,
                   sign_city_id,
                   sign_city_desc,
                   sign_agent_code,
                   sign_agent_name,
                   count(1)                            buss_all_cnt,
                   sum(if(is_buss_reach = 1, 1, 0))    buss_reach_cnt,
                   sum(if(is_pres_reach = 1, 1, 0))    pres_reach_cnt,
                   sum(if(is_effect = 1, 1, 0))        effect_cnt,
                   sum(if(taking_time_diff > 0, 1, 0)) taking_diff_cnt,
                   sum(is_taking_one_diff)             taking_one_diff_cnt,
                   sum(is_taking_two_diff)             taking_two_diff_cnt,
                   sum(is_taking_three_diff)           taking_three_diff_cnt,
                   sum(is_taking_four_diff)            taking_four_diff_cnt,
                   sum(is_taking_five_diff)            taking_five_diff_cnt,
                   sum(is_taking_six_diff)             taking_six_diff_cnt,
                   sum(is_taking_seven_diff)           taking_seven_diff_cnt,
                   date(dt)                            statistic_date,
                   dt,
                   dtype
            from jms_dm.dm_business_reach_details_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and dtype = 2
            group by dt,
                     dtype,
                     taking_provider_id,
                     taking_provider_desc,
                     taking_city_id,
                     taking_city_desc,
                     taking_agent_code,
                     taking_agent_name,
                     sign_provider_id,
                     sign_provider_desc,
                     sign_city_id,
                     sign_city_desc,
                     sign_agent_code,
                     sign_agent_name,
                     subscribe_source_code,
                     subscribe_source_name) statistics_detail
               full join (select start_city_id,
                                 start_city_desc,
                                 start_subordinate_agent_code,
                                 start_subordinate_agent_name,
                                 start_provider_id,
                                 start_provider_desc,
                                 sign_subordinate_agent_code,
                                 sign_subordinate_agent_name,
                                 sign_provider_id,
                                 sign_provider_desc,
                                 sign_city_id,
                                 sign_city_desc,
                                 island_code,
                                 island_name,
                                 sum(cainiao_pres_platform_total)     pres_total,
                                 sum(cainiao_pres_platform_reach_sum) pres_reach_sum,
                                 dt
                          from jms_dm.dm_city_pre_reach_rate_dt
                          where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                              and '{{ execution_date | cst_ds }}'
                            and cainiao_platform_name is not null
                            and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
                            and (is_pres_sign = '1'
                              or is_pres_sign = '0')
                            and date_time is not null
                          group by start_city_id,
                                   start_city_desc,
                                   start_subordinate_agent_code,
                                   start_subordinate_agent_name,
                                   start_provider_id,
                                   start_provider_desc,
                                   sign_subordinate_agent_code,
                                   sign_subordinate_agent_name,
                                   sign_provider_id,
                                   sign_provider_desc,
                                   sign_city_id,
                                   sign_city_desc,
                                   island_code,
                                   island_name,
                                   dt) net_detail on statistics_detail.taking_city_id = net_detail.start_city_id
          and statistics_detail.sign_city_id = net_detail.sign_city_id
          and statistics_detail.taking_agent_code = net_detail.start_subordinate_agent_code
          and statistics_detail.sign_agent_code = net_detail.sign_subordinate_agent_code
          and statistics_detail.taking_provider_id = net_detail.start_provider_id
          and statistics_detail.sign_provider_id = net_detail.sign_provider_id
          and statistics_detail.subscribe_source_code = net_detail.island_code
          and net_detail.dt = statistics_detail.dt
      union all
      select nvl(statistics_detail.taking_site_code, net_detail.start_pick_network_code)       taking_site_code,
             null                                                                              sign_site_code,
             nvl(statistics_detail.subscribe_source_code, net_detail.island_code)              subscribe_source_code,
             nvl(statistics_detail.customer_code, net_detail.customer_code)                    customer_code,
             nvl(statistics_detail.customer_name, net_detail.customer_name)                    customer_name,
             nvl(statistics_detail.subscribe_source_name, net_detail.island_name)              subscribe_source_name,
             nvl(statistics_detail.taking_provider_id, net_detail.start_provider_id)           taking_provider_id,
             nvl(statistics_detail.taking_provider_desc, net_detail.start_provider_desc)       taking_provider_desc,
             nvl(statistics_detail.taking_city_id, net_detail.start_city_id)                   taking_city_id,
             nvl(statistics_detail.taking_city_desc, net_detail.start_city_desc)               taking_city_desc,
             nvl(statistics_detail.taking_agent_code, net_detail.start_subordinate_agent_code) taking_agent_code,
             nvl(statistics_detail.taking_agent_name, net_detail.start_subordinate_agent_name) taking_agent_name,
             nvl(statistics_detail.taking_site_name, net_detail.start_pick_network_name)       taking_site_name,
             null                                                                              sign_provider_id,
             null                                                                              sign_provider_desc,
             null                                                                              sign_city_id,
             null                                                                              sign_city_desc,
             null                                                                              sign_agent_code,
             null                                                                              sign_agent_name,
             null                                                                              sign_site_name,
             statistics_detail.buss_all_cnt,
             statistics_detail.buss_reach_cnt,
             statistics_detail.pres_reach_cnt,
             statistics_detail.effect_cnt,
             statistics_detail.taking_diff_cnt,
             statistics_detail.taking_one_diff_cnt,
             statistics_detail.taking_two_diff_cnt,
             statistics_detail.taking_three_diff_cnt,
             statistics_detail.taking_four_diff_cnt,
             statistics_detail.taking_five_diff_cnt,
             statistics_detail.taking_six_diff_cnt,
             statistics_detail.taking_seven_diff_cnt,
             net_detail.pres_total,
             net_detail.pres_reach_sum,
             if(statistics_detail.taking_site_code is not null, 1, 0)                          flag,
             2                                                                                 statistic_type,
             nvl(statistics_detail.statistic_date, date(net_detail.dt))                        statistic_date,
             nvl(statistics_detail.dtype, 2)                                                   detail_type,
             nvl(statistics_detail.dt, net_detail.dt)                                          dt,
             nvl(statistics_detail.dtype, 2)                                                   dtype
      from (select taking_site_code,
                   subscribe_source_code,
                   customer_code,
                   customer_name,
                   subscribe_source_name,
                   taking_provider_id,
                   taking_provider_desc,
                   taking_city_id,
                   taking_city_desc,
                   taking_agent_code,
                   taking_agent_name,
                   taking_site_name,
                   count(1)                            buss_all_cnt,
                   sum(if(is_buss_reach = 1, 1, 0))    buss_reach_cnt,
                   sum(if(is_pres_reach = 1, 1, 0))    pres_reach_cnt,
                   sum(if(is_effect = 1, 1, 0))        effect_cnt,
                   sum(if(taking_time_diff > 0, 1, 0)) taking_diff_cnt,
                   sum(is_taking_one_diff)             taking_one_diff_cnt,
                   sum(is_taking_two_diff)             taking_two_diff_cnt,
                   sum(is_taking_three_diff)           taking_three_diff_cnt,
                   sum(is_taking_four_diff)            taking_four_diff_cnt,
                   sum(is_taking_five_diff)            taking_five_diff_cnt,
                   sum(is_taking_six_diff)             taking_six_diff_cnt,
                   sum(is_taking_seven_diff)           taking_seven_diff_cnt,
                   date(dt)                            statistic_date,
                   dt,
                   dtype
            from jms_dm.dm_business_reach_details_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and dtype = 2
            group by dt,
                     dtype,
                     taking_provider_id,
                     taking_provider_desc,
                     taking_city_id,
                     taking_city_desc,
                     taking_agent_code,
                     taking_agent_name,
                     taking_site_name,
                     taking_site_code,
                     subscribe_source_code,
                     customer_code,
                     customer_name,
                     subscribe_source_name) statistics_detail
               full join (select start_pick_network_code,
                                 start_pick_network_name,
                                 start_city_id,
                                 start_city_desc,
                                 start_provider_id,
                                 start_provider_desc,
                                 start_subordinate_agent_code,
                                 start_subordinate_agent_name,
                                 island_code,
                                 island_name,
                                 customer_code,
                                 customer_name,
                                 sum(cainiao_pres_platform_total)     pres_total,
                                 sum(cainiao_pres_platform_reach_sum) pres_reach_sum,
                                 dt
                          from jms_dm.dm_net_pre_reach_rate_dt
                          where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                              and '{{ execution_date | cst_ds }}'
                            and cainiao_platform_name is not null
                            and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
                            and (is_pres_sign = '1'
                              or is_pres_sign = '0')
                            and date_time is not null
                          group by start_pick_network_code,
                                   start_pick_network_name,
                                   start_city_id,
                                   start_city_desc,
                                   start_provider_id,
                                   start_provider_desc,
                                   start_subordinate_agent_code,
                                   start_subordinate_agent_name,
                                   island_code,
                                   island_name,
                                   customer_code,
                                   customer_name,
                                   dt) net_detail
                         on statistics_detail.taking_site_code = net_detail.start_pick_network_code
                             and statistics_detail.taking_city_id = net_detail.start_city_id
                             and statistics_detail.taking_provider_id = net_detail.start_provider_id
                             and statistics_detail.taking_agent_code = net_detail.start_subordinate_agent_code
                             and statistics_detail.subscribe_source_code = net_detail.island_code
                             and statistics_detail.subscribe_source_name = net_detail.island_name
                             and statistics_detail.customer_code = net_detail.customer_code
                             and statistics_detail.customer_name = net_detail.customer_name
                             and net_detail.dt = statistics_detail.dt
) detail distribute by dt, dtype, PMOD(HASH(RAND()), 3);





--时效签收
insert overwrite table jms_dm.dm_business_reach_summary_dt partition (dt, dtype)
select *
from (select null                                                                              taking_site_code,
             null                                                                              sign_site_code,
             nvl(statistics_detail.subscribe_source_code, net_detail.island_code)              subscribe_source_code,
             null                                                                              customer_code,
             null                                                                              customer_name,
             nvl(statistics_detail.subscribe_source_name, net_detail.island_name)              subscribe_source_name,
             nvl(statistics_detail.taking_provider_id, net_detail.start_provider_id)           taking_provider_id,
             nvl(statistics_detail.taking_provider_desc, net_detail.start_provider_desc)       taking_provider_desc,
             nvl(statistics_detail.taking_city_id, net_detail.start_city_id)                   taking_city_id,
             nvl(statistics_detail.taking_city_desc, net_detail.start_city_desc)               taking_city_desc,
             nvl(statistics_detail.taking_agent_code, net_detail.start_subordinate_agent_code) taking_agent_code,
             nvl(statistics_detail.taking_agent_name, net_detail.start_subordinate_agent_name) taking_agent_name,
             null                                                                              taking_site_name,
             nvl(statistics_detail.sign_provider_id, net_detail.sign_provider_id)              sign_provider_id,
             nvl(statistics_detail.sign_provider_desc, net_detail.sign_provider_desc)          sign_provider_desc,
             nvl(statistics_detail.sign_city_id, net_detail.sign_city_id)                      sign_city_id,
             nvl(statistics_detail.sign_city_desc, net_detail.sign_city_desc)                  sign_city_desc,
             nvl(statistics_detail.sign_agent_code, net_detail.sign_subordinate_agent_code)    sign_agent_code,
             nvl(statistics_detail.sign_agent_name, net_detail.sign_subordinate_agent_name)    sign_agent_name,
             null                                                                              sign_site_name,
             statistics_detail.buss_all_cnt,
             statistics_detail.buss_reach_cnt,
             statistics_detail.pres_reach_cnt,
             statistics_detail.effect_cnt,
             statistics_detail.taking_diff_cnt,
             statistics_detail.taking_one_diff_cnt,
             statistics_detail.taking_two_diff_cnt,
             statistics_detail.taking_three_diff_cnt,
             statistics_detail.taking_four_diff_cnt,
             statistics_detail.taking_five_diff_cnt,
             statistics_detail.taking_six_diff_cnt,
             statistics_detail.taking_seven_diff_cnt,
             net_detail.pres_total,
             net_detail.pres_reach_sum,
             if(statistics_detail.taking_city_id is not null, 1, 0)                            flag,
             1                                                                                 statistic_type,
             nvl(statistics_detail.statistic_date, date(net_detail.dt))                        statistic_date,
             nvl(statistics_detail.dtype, 3)                                                   detail_type,
             nvl(statistics_detail.dt, net_detail.dt)                                          dt,
             nvl(statistics_detail.dtype, 3)                                                   dtype
      from (select subscribe_source_code,
                   subscribe_source_name,
                   taking_provider_id,
                   taking_provider_desc,
                   taking_city_id,
                   taking_city_desc,
                   taking_agent_code,
                   taking_agent_name,
                   sign_provider_id,
                   sign_provider_desc,
                   sign_city_id,
                   sign_city_desc,
                   sign_agent_code,
                   sign_agent_name,
                   count(1)                            buss_all_cnt,
                   sum(if(is_buss_reach = 1, 1, 0))    buss_reach_cnt,
                   sum(if(is_pres_reach = 1, 1, 0))    pres_reach_cnt,
                   sum(if(is_effect = 1, 1, 0))        effect_cnt,
                   sum(if(taking_time_diff > 0, 1, 0)) taking_diff_cnt,
                   sum(is_taking_one_diff)             taking_one_diff_cnt,
                   sum(is_taking_two_diff)             taking_two_diff_cnt,
                   sum(is_taking_three_diff)           taking_three_diff_cnt,
                   sum(is_taking_four_diff)            taking_four_diff_cnt,
                   sum(is_taking_five_diff)            taking_five_diff_cnt,
                   sum(is_taking_six_diff)             taking_six_diff_cnt,
                   sum(is_taking_seven_diff)           taking_seven_diff_cnt,
                   date(dt)                            statistic_date,
                   dt,
                   dtype
            from jms_dm.dm_business_reach_details_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and dtype = 3
            group by dt,
                     dtype,
                     taking_provider_id,
                     taking_provider_desc,
                     taking_city_id,
                     taking_city_desc,
                     taking_agent_code,
                     taking_agent_name,
                     sign_provider_id,
                     sign_provider_desc,
                     sign_city_id,
                     sign_city_desc,
                     sign_agent_code,
                     sign_agent_name,
                     subscribe_source_code,
                     subscribe_source_name) statistics_detail
               full join (select start_city_id,
                                 start_city_desc,
                                 start_subordinate_agent_code,
                                 start_subordinate_agent_name,
                                 start_provider_id,
                                 start_provider_desc,
                                 sign_subordinate_agent_code,
                                 sign_subordinate_agent_name,
                                 sign_provider_id,
                                 sign_provider_desc,
                                 sign_city_id,
                                 sign_city_desc,
                                 island_code,
                                 island_name,
                                 sum(cainiao_pres_platform_total)     pres_total,
                                 sum(cainiao_pres_platform_reach_sum) pres_reach_sum,
                                 dt
                          from jms_dm.dm_city_pre_reach_rate_sign_dt
                          where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                              and '{{ execution_date | cst_ds }}'
                            and cainiao_platform_name is not null
                            and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
                            and (is_pres_sign = '1'
                              or is_pres_sign = '0')
                            and date_time is not null
                          group by start_city_id,
                                   start_city_desc,
                                   start_subordinate_agent_code,
                                   start_subordinate_agent_name,
                                   start_provider_id,
                                   start_provider_desc,
                                   sign_subordinate_agent_code,
                                   sign_subordinate_agent_name,
                                   sign_provider_id,
                                   sign_provider_desc,
                                   sign_city_id,
                                   sign_city_desc,
                                   island_code,
                                   island_name,
                                   dt) net_detail on statistics_detail.taking_city_id = net_detail.start_city_id
          and statistics_detail.sign_city_id = net_detail.sign_city_id
          and statistics_detail.taking_agent_code = net_detail.start_subordinate_agent_code
          and statistics_detail.sign_agent_code = net_detail.sign_subordinate_agent_code
          and statistics_detail.taking_provider_id = net_detail.start_provider_id
          and statistics_detail.sign_provider_id = net_detail.sign_provider_id
          and statistics_detail.subscribe_source_code = net_detail.island_code
          and net_detail.dt = statistics_detail.dt
      union all
      select nvl(statistics_detail.taking_site_code, net_detail.start_pick_network_code)       taking_site_code,
             null                                                                              sign_site_code,
             nvl(statistics_detail.subscribe_source_code, net_detail.island_code)              subscribe_source_code,
             nvl(statistics_detail.customer_code, net_detail.customer_code)                    customer_code,
             nvl(statistics_detail.customer_name, net_detail.customer_name)                    customer_name,
             nvl(statistics_detail.subscribe_source_name, net_detail.island_name)              subscribe_source_name,
             nvl(statistics_detail.taking_provider_id, net_detail.start_provider_id)           taking_provider_id,
             nvl(statistics_detail.taking_provider_desc, net_detail.start_provider_desc)       taking_provider_desc,
             nvl(statistics_detail.taking_city_id, net_detail.start_city_id)                   taking_city_id,
             nvl(statistics_detail.taking_city_desc, net_detail.start_city_desc)               taking_city_desc,
             nvl(statistics_detail.taking_agent_code, net_detail.start_subordinate_agent_code) taking_agent_code,
             nvl(statistics_detail.taking_agent_name, net_detail.start_subordinate_agent_name) taking_agent_name,
             nvl(statistics_detail.taking_site_name, net_detail.start_pick_network_name)       taking_site_name,
             null                                                                              sign_provider_id,
             null                                                                              sign_provider_desc,
             null                                                                              sign_city_id,
             null                                                                              sign_city_desc,
             null                                                                              sign_agent_code,
             null                                                                              sign_agent_name,
             null                                                                              sign_site_name,
             statistics_detail.buss_all_cnt,
             statistics_detail.buss_reach_cnt,
             statistics_detail.pres_reach_cnt,
             statistics_detail.effect_cnt,
             statistics_detail.taking_diff_cnt,
             statistics_detail.taking_one_diff_cnt,
             statistics_detail.taking_two_diff_cnt,
             statistics_detail.taking_three_diff_cnt,
             statistics_detail.taking_four_diff_cnt,
             statistics_detail.taking_five_diff_cnt,
             statistics_detail.taking_six_diff_cnt,
             statistics_detail.taking_seven_diff_cnt,
             net_detail.pres_total,
             net_detail.pres_reach_sum,
             if(statistics_detail.taking_site_code is not null, 1, 0)                          flag,
             2                                                                                 statistic_type,
             nvl(statistics_detail.statistic_date, date(net_detail.dt))                        statistic_date,
             nvl(statistics_detail.dtype, 3)                                                   detail_type,
             nvl(statistics_detail.dt, net_detail.dt)                                          dt,
             nvl(statistics_detail.dtype, 3)                                                   dtype
      from (select taking_site_code,
                   subscribe_source_code,
                   customer_code,
                   customer_name,
                   subscribe_source_name,
                   taking_provider_id,
                   taking_provider_desc,
                   taking_city_id,
                   taking_city_desc,
                   taking_agent_code,
                   taking_agent_name,
                   taking_site_name,
                   count(1)                            buss_all_cnt,
                   sum(if(is_buss_reach = 1, 1, 0))    buss_reach_cnt,
                   sum(if(is_pres_reach = 1, 1, 0))    pres_reach_cnt,
                   sum(if(is_effect = 1, 1, 0))        effect_cnt,
                   sum(if(taking_time_diff > 0, 1, 0)) taking_diff_cnt,
                   sum(is_taking_one_diff)             taking_one_diff_cnt,
                   sum(is_taking_two_diff)             taking_two_diff_cnt,
                   sum(is_taking_three_diff)           taking_three_diff_cnt,
                   sum(is_taking_four_diff)            taking_four_diff_cnt,
                   sum(is_taking_five_diff)            taking_five_diff_cnt,
                   sum(is_taking_six_diff)             taking_six_diff_cnt,
                   sum(is_taking_seven_diff)           taking_seven_diff_cnt,
                   date(dt)                            statistic_date,
                   dt,
                   dtype
            from jms_dm.dm_business_reach_details_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and dtype = 3
            group by dt,
                     dtype,
                     taking_provider_id,
                     taking_provider_desc,
                     taking_city_id,
                     taking_city_desc,
                     taking_agent_code,
                     taking_agent_name,
                     taking_site_name,
                     taking_site_code,
                     subscribe_source_code,
                     customer_code,
                     customer_name,
                     subscribe_source_name) statistics_detail
               full join (select start_pick_network_code,
                                 start_pick_network_name,
                                 start_city_id,
                                 start_city_desc,
                                 start_provider_id,
                                 start_provider_desc,
                                 start_subordinate_agent_code,
                                 start_subordinate_agent_name,
                                 island_code,
                                 island_name,
                                 customer_code,
                                 customer_name,
                                 sum(cainiao_pres_platform_total)     pres_total,
                                 sum(cainiao_pres_platform_reach_sum) pres_reach_sum,
                                 dt
                          from jms_dm.dm_net_pre_reach_rate_sign_dt
                          where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                              and '{{ execution_date | cst_ds }}'
                            and cainiao_platform_name is not null
                            and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
                            and (is_pres_sign = '1'
                              or is_pres_sign = '0')
                            and date_time is not null
                          group by start_pick_network_code,
                                   start_pick_network_name,
                                   start_city_id,
                                   start_city_desc,
                                   start_provider_id,
                                   start_provider_desc,
                                   start_subordinate_agent_code,
                                   start_subordinate_agent_name,
                                   island_code,
                                   island_name,
                                   customer_code,
                                   customer_name,
                                   dt) net_detail
                         on statistics_detail.taking_site_code = net_detail.start_pick_network_code
                             and statistics_detail.taking_city_id = net_detail.start_city_id
                             and statistics_detail.taking_provider_id = net_detail.start_provider_id
                             and statistics_detail.taking_agent_code = net_detail.start_subordinate_agent_code
                             and statistics_detail.subscribe_source_code = net_detail.island_code
                             and statistics_detail.subscribe_source_name = net_detail.island_name
                             and statistics_detail.customer_code = net_detail.customer_code
                             and statistics_detail.customer_name = net_detail.customer_name
                             and net_detail.dt = statistics_detail.dt
) detail distribute by dt, dtype, PMOD(HASH(RAND()), 3);
